<?php
define('SQL_HOST','');//数据库地址
define("SQL_USER","root");//数据库用户名
define("SQL_PASSWORD","root");//数据库密码
define("SQL_DATABASE","mall");//连接的数据库名字
define("SQL_PORT","3306");//数据库端口号,默认为3306
define("File_LOG","./log");//日志文档

/**
 *
 */
class EmileShell
{
   public static function sql() {
		//链接sql
		$mysql = mysqli_connect(SQL_HOST,SQL_USER,SQL_PASSWORD,SQL_DATABASE,SQL_PORT);
        if(!$mysql )  echo '连接数据库失败！';
        //切换数据库
        //mysqli_select_db(SQLDATABASE);
		//查询语句
        $sql = "SELECT
                        m.username,
                        m.email,
                        a.province,
                        a.city,
                        a.region,
                        a.detail_address
                FROM
                        member m
                        LEFT JOIN ums_member_receive_address a ON m.id = a.member_id
                WHERE
                        m.email LIKE '%sk+%'
                        AND a.delete_state = 0";
        //查询
        $result= mysqli_query($mysql,$sql);//运行sql
        if(mysqli_num_rows($result) > 0){
            $sql2 = "insert into `sms_unright_adress`(province,city,region,detail_address,create_time,add_mode,create_user) ";
            $k=0;
            $sql2 .=" VALUES(";
            while($row = mysqli_fetch_assoc($result)) {
                if($k > 0){
                    $sql2 .=",(";
                }
                $sql3 = "SELECT id FROM sms_unright_adress WHERE
                            province LIKE '%{$row['province']}%'
                            AND city LIKE '%{$row['city']}%'
                            AND region LIKE '%{$row['region']}%'
                            AND detail_address LIKE '%{$row['detail_address']}%'";
                $result3 = mysqli_query($mysql,$sql3);//运行sql
                if(mysqli_num_rows($result3) > 0) continue;
                $sql2 .= '"';
                $sql2 .= $row['province'] ?? '';//省份/直辖市
                $sql2 .= '","';
                $sql2 .= $row['city'] ?? '';//城市
                $sql2 .= '","';
                $sql2 .= $row['region'] ?? '';//区
                $sql2 .= '","';
                $sql2 .= $row['detail_address'] ?? '';//详细地址(街道)
                $sql2 .= '","';
                $sql2 .= date('Y-m-d H:i:s');//创建时间 create_time
                $sql2 .= '",';
                $sql2 .= 2;//add_mode 添加方式1/手工创建 2/系统添加
                $sql2 .= ',"';
                $sql2 .= '系统添加';//create_user 创建人
                $sql2 .= '")';
                $k++;
                $log_data[] = $row;
            }
            if(!empty($log_data)){
                var_export($log_data);
                self::write_log(json_encode($log_data,true));
                $result2 = mysqli_query($mysql,$sql2);
                if($result2)
                    echo '写入成功';
                else
                    echo '写入失败';
            }else{
                echo '无匹配字段';
            }
        }else{
            echo '无匹配值';
        }
        //关闭连接
        mysqli_close($mysql);
    }

   protected static function write_log($log){

        $file= File_LOG.'/shell_'.date('y-m-d').'.log';
        if(!is_file($file)){
            file_put_contents($file,'',FILE_APPEND);//如果文件不存在，则创建一个新文件。
        }
        $contents=$log."\r\n";
        file_put_contents($file, $contents,FILE_APPEND);
    }
}
(new  EmileShell($data))::sql();

/*
1.查询
```bash
SELECT  字段1，字段2，字段3，FROM 表名 WHERE 条件一 AND 条件二 ORDER BY 字段 desc
```
示例
```sql
SELECT
   m.username,
   m.email,
   a.province,
   a.city,
   a.region,
   a.detail_address
FROM
   member m
   LEFT JOIN ums_member_receive_address a ON m.id = a.member_id
WHERE
   m.email LIKE '%223232%'
   AND a.delete_state = 0
```

2.写入数据需要注意，写入方式有多种，个人觉得这种方式比较清晰，VALUES（），（）里的值要和前面的字段值要对应
```sql
insert into `表名`(字段1,字段2,字段3...) VALUES(写入值1，写入值2，写入值3...),(写入值1，写入值2，写入值3...)
```
示例
```sql
insert into `sms_unright_adress`
(province,city,region,detail_address)
VALUES('北京','上海','西安','南京'),('北京','上海','西安','南京')
```
3.修改 记得测试 WHERE 条件 是否生效 可以使用查询打印值
```sql
UPDATE `表名` SET 字段名1=值,字段名2=值,字段名2=值 WHERE 
条件
```

示例
```sql
UPDATE `sms_unright_adress` SET province='北京',city='上海' WHERE  id = 16
```
4.删除
```sql
DELETE FROM `表名` WHERE  条件
```

示例
```sql
DELETE FROM `sms_unright_adress` WHERE  id = 16
```
*/
?>